The present disclosure relates to report generation, and more specifically, to techniques for generating reports for a business entity through the use of accumulator tables and without join operations for multiple database tables.
In a database management system (DBMS), data can be stored in one or more data containers and each container can contain records. Data within each of the records can be organized into one or more fields. In a relational database system, such data containers are typically referred to as tables, the records are typically referred to as rows, and the fields are referred to as columns. Generally, in database environments, users store, update and retrieve information by submitting commands (e.g., a query) to a database server. In order for such commands to be processed correctly, the commands must comply with a language supported by the database server (e.g., Structured Query Language (SQL)).
Generally, a “join” refers to a query that combines rows from two or more tables within a database. For example, for a query composed using SQL, a join is performed whenever multiple tables appear in the FROM clause of the SQL query. The columns of data to be retrieved from the tables can be listed in the SQL query's SELECT clause, and the query's SELECT list can include columns from any of the base tables listed in the FROM clause. Typically, most join queries contain WHERE clause conditions that compare two columns, each from a different table, and such a condition is commonly referred to as a join condition. When a DBMS executes a join, the DBMS combines pairs of rows for which the join condition evaluates to TRUE, where each pair contains one row from each table. When executing a join involving three or more tables, a DBMS can join two of the tables based on the join conditions comparing their columns, and the DBMS can then join the result to another one of the tables based on join conditions containing columns of the already joined tables and the other table. The DBMS could then continue this process until all tables specified in the query are joined into the result. Generally, such queries are much more computationally expensive than queries that reference a single table and joins involving many different tables can involve a significant number of row combinations and thus can consume considerable computational resources, particularly when dealing with tables containing a significant number of rows.
In a retail environment, businesses oftentimes generate reports for business transaction data on a regular basis. For example, a business could generate a daily accounting report at a particular time each day, e.g., 8:00 pm if the business closes at 8:00 pm daily. One consequence to generating reports according to a fixed schedule is that, in practice, businesses may not operate exactly according to such a fixed schedule. For example, although the business may post their closing time as 8:00 pm, the business could stay open slightly later on nights when multiple customers are still in the store at 8:00 pm. However, if the reports are configured to run at exactly 8:00 pm, the generated reports may not include the transaction data for the business generated after 8:00 pm. As a result, the reports may be incomplete and the reports may need to be run regenerated, thus wasting computational resources.